Amazon Redshift Spectrum ダブルクォーテーションで囲まれた文字列をクエリする方法

はじめに

Amazon Athena の Hive DDL がダブルクォーテーションマーク (") をサポートしているのに対して、Amazon Redshift はシングルクォーテーションマーク (') を使用します。そのため、Amazon Redshift Spectrumでダブルクォーテーションマーク (")が含まれているファイルをクエリーするとうまくパースできません。そこで、Amazon Redshift Spectrum ダブルクォーテーションで囲まれた文字列にクエリするワークアラウンドについて解説します。

Amazon Redshift で DDL Hive を使用する際の構文エラー

Amazon Redshift は、Hive DDL と類似した CREATE EXTERNAL TABLE 用のデータ定義言語 (DDL) をサポートしています。ただし、2 つの DDL タイプは常に代替可能であるとは限りません。Hive DDL をコピーして Amazon Redshift 外部テーブルを作成または変更した場合、構文エラーが発生する可能性があります。以下に Amazon Redshift と Hive DDL の違いの例を示します。

• Hive DDL がダブルクォーテーションマーク (") をサポートしているのに対して、Amazon Redshift はシングルクォーテーションマーク (') を使用します。
• Amazon Redshift は STRING データタイプをサポートしていません。代わりに VARCHAR を使用します。

引用：Amazon Redshift Spectrum のクエリのトラブルシューティング

ケース１：ダブルクォーテーションで括られた数値・日付データが認識できない

これは簡単です。Amazon Athena の場合と同じように OpenCSVSerdeを利用して、テーブル定義・クエリの実行することが可能です。

Amazon Athena 新しくサポートされた OpenCSVSerDeを使ってみた

では、実際にやってみましょう。以下のデータ(users_from_athena.csv)があるとします。

"id","name","created_at" "1","foo","2018-02-01" "2","bar","2018-02-02" "3","baz","2018-02-03"

以下のようにRedshiftに外部テーブルを定義します。

CREATE EXTERNAL TABLE .users_from_athena(
id varchar(255),
name varchar(255),
created_at varchar(255)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'escapeChar'='\\',
'quoteChar'='\"',
'separatorChar'=',')
STORED AS TEXTFILE
LOCATION 's3:///users_from_athena/'
TABLE PROPERTIES (
'classification'='csv',
'skip.header.line.count'='1',
'typeOfData'='file')
;

以下のようにクエリを実行できます。

cmdb=> select * from .users_from_athena;
id | name | created_at |
----+-------+------------+
1 | foo | 2018-02-01 |
2 | bar | 2018-02-02 |
3 | baz | 2018-02-03 |
(3 rows)

Time: 1273.384 ms (00:01.273)

OpenCsvSerdeは、データ型は全て文字列型として定義しています。もし、数値型や日付型等の別のデータ型として利用したい場合は、上記のクエリをサブクエリーとして型変換すると良いでしょう。

ケース２：AthenaとSpectrumのRegexSerDeでパースの結果が異なる?!

デフォルトで登録されている Amazon Athenaのsampledbのelb_logsテーブルを Amazon Redshift Spectrumから参照したところ、うまくパースができないようで、カラムの内容が異なりました。下記のxx行目が途中から表示されています。

cmdb=> select * from sampledb.elb_logs limit 1;
-[ RECORD 1 ]---------+----------------------------------------------------------------------------
timestamp | 2014-09-25T23:00:23.107160Z
elbname | lb-demo
requestip | 245.121.88.40
requestport | 17141
backendip | 244.193.242.133
backendport | 8888
requestprocessingtime | 9e-05
backendprocessingtime | 0.050973
clientresponsetime | 4.8e-05
elbresponsecode | 200
backendresponsecode | 200
receivedbytes | 0
sentbytes | 2
requestverb | GET
url | ttp://www.abcxyz.com:80/jobbrowser/?format=json&state=running&user=9u45ok8
protocol | TP/1.1" +
|

テーブル定義を保存している AWS Glue Data Catalogは同じなので、テーブル定義はAthenaとSpectrumで共通なので同じ結果が得られるはず、、、と当初は想定していました。

• Hive DDL がダブルクォーテーションマーク (") をサポートしているのに対して、Amazon Redshift はシングルクォーテーションマーク (') を使用します。

Redshift Spectrum は ダブルクォーテーションではなくシングルクオーテーションをサポートするなど、必ずしも Athena と同一の Hive DDLと同じ結果を得られるとは限りません。上記の仕様の差異により、Redshift Spectrum は、ダブルクォーテーションで囲まれた文字列を正常にパースできない状況が発生しているものとを考えられます。

ワークアラウンドとしては、Parquet に変換したデータを Reshift Spectrum から参照するか、もしくはダブルクォーテーションで囲まれた文字列を1つの列として扱うよう DDL を変更します。

ダブルクォーテーションで囲まれた文字列を1つの列とした場合のDDLは以下のようになります。requestがダブルクォーテーションで囲まれた文字列なので、正規表現で取り除きます。

CREATE EXTERNAL TABLE `elb_logs_for_spectrum`(
`timestamp` string COMMENT '',
`elbname` string COMMENT '',
`requestip` string COMMENT '',
`requestport` int COMMENT '',
`backendip` string COMMENT '',
`backendport` int COMMENT '',
`requestprocessingtime` double COMMENT '',
`backendprocessingtime` double COMMENT '',
`clientresponsetime` double COMMENT '',
`elbresponsecode` string COMMENT '',
`backendresponsecode` string COMMENT '',
`receivedbytes` bigint COMMENT '',
`sentbytes` bigint COMMENT '',
`request` string COMMENT '')
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'input.regex'='([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \"(.*)\"$')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://us-east-1.elasticmapreduce.samples/elb-access-logs/data'
TBLPROPERTIES (
'transient_lastDdlTime'='1508595294')

クエリを実行してみると、requestがそのまま表示されています。

cmdb=> select * from mysampledb.elb_logs_for_spectrum limit 1;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------
timestamp | 2014-09-25T21:28:40.726570Z
elbname | lb-demo
requestip | 242.183.64.11
requestport | 67890
backendip | 252.194.243.95
backendport | 443
requestprocessingtime | 9e-05
backendprocessingtime | 0.047148
clientresponsetime | 7.9e-05
elbresponsecode | 200
backendresponsecode | 400
receivedbytes | 0
sentbytes | 2
request | GET http://www.abcxyz.com:80/jobbrowser/?format=json&state=running&user=9u45ok8 HTTP/1.1

最後に

ケース１で紹介した OpenCSVSerdeを利用すると、Athenaの実行結果のファイルも直接パースできますので、AthenaとRedshift Spectrumの連携などもおすすめです。Amazon Redshift はシングルクォーテーションマーク (') を使用するという仕様は、Hive DDLを使い慣れた人にとってはハマりポイントと感じました。ケース２の場合はダブルクォーテーションで囲まれた文字列を1つの列として扱い、Redshiftの関数でパースするというのも一つの方法ではないかと思います。